package net.suncaper.hotel.mapper.impl;

import net.suncaper.hotel.common.utils.JDBCUtils;
import net.suncaper.hotel.mapper.IUserMapper;
import net.suncaper.hotel.model.domain.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 用户数据访问实现类
 */
public class UserMapperImpl implements IUserMapper {
    private QueryRunner queryRunner = new QueryRunner();

    @Override
    public User findByUsername(String username) {
        String sql = "SELECT * FROM user WHERE username = ?";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql, 
                new BeanHandler<>(User.class), username);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public User findById(Integer id) {
        String sql = "SELECT * FROM user WHERE id = ?";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql, 
                new BeanHandler<>(User.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<User> findAll() {
        String sql = "SELECT * FROM user ORDER BY create_time DESC";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql, 
                new BeanListHandler<>(User.class));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<User> findByPage(int offset, int pageSize) {
        String sql = "SELECT * FROM user ORDER BY create_time DESC LIMIT ?, ?";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql, 
                new BeanListHandler<>(User.class), offset, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM user";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql, 
                new ScalarHandler<Long>());
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int insert(User user) {
        String sql = "INSERT INTO user (username, password, real_name, phone, email, role, status) " +
                     "VALUES (?, ?, ?, ?, ?, ?, ?)";
        try {
            return queryRunner.update(JDBCUtils.getConnection(), sql, 
                user.getUsername(), user.getPassword(), user.getRealName(),
                user.getPhone(), user.getEmail(), user.getRole(), user.getStatus());
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int update(User user) {
        String sql = "UPDATE user SET real_name = ?, phone = ?, email = ?, " +
                     "role = ?, status = ? WHERE id = ?";
        try {
            return queryRunner.update(JDBCUtils.getConnection(), sql, 
                user.getRealName(), user.getPhone(), user.getEmail(),
                user.getRole(), user.getStatus(), user.getId());
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int delete(Integer id) {
        String sql = "DELETE FROM user WHERE id = ?";
        try {
            return queryRunner.update(JDBCUtils.getConnection(), sql, id);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }
}